package cn.ztydata.teachingsystem.heaven.dao;

import cn.ztydata.teachingsystem.common.Page;
import cn.ztydata.teachingsystem.heaven.entity.CourseArrange;
import org.apache.commons.lang3.StringUtils;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSourceUtils;
import org.springframework.stereotype.Repository;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;

/**
 * 课程安排dao
 * Created by wyh on 2015/3/30.
 */
@Repository
public class CourseArrangeDao extends BaseDao {

    /**
     * 获取与数据库中重复的课程代码
     * @param courseArrangeCodeSet 课程安排课程代码集合
     * @return String
     *
     * @author wyh
     * @since 2015-03-26
     */
    public String getDuplicateCode(final Set<String> courseArrangeCodeSet) {
        if (courseArrangeCodeSet.isEmpty()) {
            log.debug("导入的课程安排课程代码集合为空");

            log.info("课程安排课程代码集合为0");
            return null;
        }

        try {
            String sql = "SELECT sCode FROM t_course_arrange WHERE sCode IN (:numSet) LIMIT 1";
            SqlParameterSource sps = new MapSqlParameterSource().addValue("numSet", courseArrangeCodeSet);

            log.debug("return 表中与导入数据重复的课程安排课程代码");
            return namedParameterJdbcTemplate.queryForObject(sql, sps, String.class);
        } catch (EmptyResultDataAccessException ex) {
            log.debug("数据库中无重复的课程安排课程代码");

            log.info("数据库中无重复的课程安排课程代码");
            return null;
        }
    }

    /**
     * 批量保存课程安排
     * @param courseArrangeList 课程安排List
     * @return int[]
     *
     * @author wyh
     * @since 2015-03-26
     */
    public int[] saveCourseArranges(final List<CourseArrange> courseArrangeList){
        String sql = "INSERT INTO t_course_arrange VALUES(NULL, :code, :departNum, :startYear, :endYear, :term, :courseType)";

        SqlParameterSource[] batch = SqlParameterSourceUtils.createBatch(courseArrangeList.toArray());
        log.debug("return 批量导入课程安排数据的结果");
        return namedParameterJdbcTemplate.batchUpdate(sql, batch);
    }

    /**
     * 根据系部编码查询课程
     * @param dept 系部代码
     * @return List
     *
     * @author wyh
     * @since 2015-03-30
     */
    public List<Map<String, Object>> getCourseList(String dept){
        StringBuilder sql = new StringBuilder("SELECT c.id, c.sName FROM t_department d, t_course c WHERE d.id=c.iDeptId");
        MapSqlParameterSource sps = new MapSqlParameterSource();

        if(!dept.equals("0")){
            log.debug("系部不为0：!dept.equals(\"0\")");
            sql.append(" AND d.sNumber=:dept ");
            sps.addValue("dept", dept);
        }

        log.debug("返回 查询课程");
        return namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<Map<String, Object>>() {
            @Override
            public Map<String, Object> mapRow(ResultSet resultSet, int i) throws SQLException {
                Map<String, Object> result = new HashMap<>();
                result.put("id", resultSet.getLong("id"));
                result.put("name", resultSet.getString("sName"));
                return result;
            }
        });
    }

    /**
     * 课程安排列表
     * @param depart 系部代码
     * @param codeList 课程代码集合
     * @param startYear 起始学年
     * @param term 学期 0上1下2全部
     * @param page 页码
     * @param pageSize  页数
     * @return page
     *
     * @author wyh
     * @since 2015-03-31
     */
    public Page<CourseArrange> listArrange(String depart, List<String> codeList, String startYear, int term, int page, int pageSize){
        StringBuilder sql = new StringBuilder("SELECT d.sName, ca.* FROM t_department d RIGHT JOIN t_course_arrange ca ON d.sNumber=ca.sDeptNum ");
        StringBuilder sqlTotal = new StringBuilder("SELECT COUNT(*) FROM t_department d RIGHT JOIN t_course_arrange ca ON d.sNumber=ca.sDeptNum ");
        StringBuilder where = new StringBuilder(" WHERE 0=0 ");
        MapSqlParameterSource sps = new MapSqlParameterSource();

        //筛选系部
        if(!StringUtils.isEmpty(depart)){
            log.debug("系部不为空：!StringUtils.isEmpty(depart)");
            where.append(" AND ca.sDeptNum=:depart ");
            sps.addValue("depart", depart);
        }

        //筛选课程
        if(codeList != null){
            if(codeList.isEmpty()){
                log.debug("课程代码集合为空：codeList.isEmpty()");
                return new Page<>();
            }
            where.append(" AND ca.sCode IN(:codeList) ");
            sps.addValue("codeList", codeList);
        }

        //筛选起始年份
        if(!StringUtils.isEmpty(startYear)){
            log.debug("起始学年不为空：!StringUtils.isEmpty(startYear)");
            where.append(" AND ca.sStartYear=:startYear ");
            sps.addValue("startYear", startYear);
        }

        //筛选学期
        if(term != 2){
            log.debug("筛选学期：term != 2");
            where.append(" AND ca.tiTerm=:term ");
            sps.addValue("term", term);
        }

        int total = namedParameterJdbcTemplate.queryForObject(sqlTotal.append(where).toString(), sps, Integer.class);
        if(total == 0){
            log.debug("查询总数为0：total == 0");
            return new Page<>();
        }

        sql.append(where).append(" ORDER BY sStartYear DESC, sEndYear DESC, tiTerm DESC ").append(getLimitStr(page, pageSize));

        List<CourseArrange> list = namedParameterJdbcTemplate.query(sql.toString(), sps, new RowMapper<CourseArrange>() {
            @Override
            public CourseArrange mapRow(ResultSet resultSet, int i) throws SQLException {
                CourseArrange courseArrange = new CourseArrange();
                courseArrange.setId(resultSet.getLong("id"));
                courseArrange.setCode(resultSet.getString("sCode"));
                courseArrange.setDepartName(resultSet.getString("sName"));
                courseArrange.setStartYear(resultSet.getString("sStartYear"));
                courseArrange.setEndYear(resultSet.getString("sEndYear"));
                courseArrange.setTerm(resultSet.getInt("tiTerm"));
                courseArrange.setCourseType(resultSet.getInt("tiCourseType"));
                return courseArrange;
            }
        });

        log.debug("返回 课程安排列表");
        return new Page<>(total, pageSize, page, list);
    }

    /**
     * 学年下拉框
     * @return List
     *
     * @author wyh
     * @since 2015-4-16
     */
    public List<String> getYearList() {
        String sql = "SELECT DISTINCT sStartYear FROM t_course_arrange ORDER BY sStartYear";

        log.debug("返回 所有学年");
        return namedParameterJdbcTemplate.queryForList(sql, new HashMap<String, Object>(), String.class);
    }
}
